﻿Imports System.Data.OleDb

Public Class GiaoVienDAO
    Dim sDBName As String = "Database\thietkethoikhoabieu.mdb"

    Public Function LayDanhSachGiaoVien() As List(Of GiaoVienDTO)
        Dim str = "SELECT * FROM GiaoVien"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of GiaoVienDTO)

        While reader.Read()
            Dim gv As New GiaoVienDTO
            gv.MaGV = reader.GetString(0)
            gv.TenGV = reader.GetString(1)
            gv.NgaySinh = reader.GetDateTime(2)
            gv.SoDienThoai = reader.GetString(3)
            gv.DiaChi = reader.GetString(4)
            gv.Email = reader.GetString(5)
            gv.GioiTinh = reader.GetString(6)
            ds.Add(gv)
        End While

        conn.Close()
        Return ds
    End Function

    Public Function ThemGiaoVien(ByVal gv As GiaoVienDTO) As Integer
        Dim str = String.Format("INSERT INTO GiaoVien(MaGV, TenGV, NgaySinh, SoDienThoai, DiaChi,Email,GioiTinh) " _
                & "values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", gv.MaGV, gv.TenGV, gv.NgaySinh, _
                gv.SoDienThoai, gv.DiaChi, gv.Email, gv.GioiTinh)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()
        conn.Close()

        Return ketqua
    End Function

    Public Function XoaGiaoVien(ByVal gv As GiaoVienDTO) As Integer
        Dim str = String.Format("DELETE FROM GiaoVien WHERE MaGV='{0}'", gv.MaGV)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function SuaGiaoVien(ByVal gv As GiaoVienDTO) As Integer
        Dim str = String.Format("UPDATE GiaoVien SET TenGV='{0}', NgaySinh='{1}', SoDienThoai='{2}', " _
                & " DiaChi='{3}', Email='{4}', GioiTinh='{5}' WHERE MaGV='{6}'", gv.TenGV, gv.NgaySinh, _
                gv.SoDienThoai, gv.DiaChi, gv.Email, gv.GioiTinh, gv.MaGV)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function TraCuuGiaoVien(ByVal gv As GiaoVienDTO) As List(Of GiaoVienDTO)
        Dim sSelect = String.Format("SELECT * FROM GiaoVien WHERE MaGV LIKE '%{0}%' AND " _
                    & "TenGV LIKE '%{1}%' AND NgaySinh LIKE '%{2}%' AND SoDienThoai LIKE '%{3}%' " _
                    & "AND DiaChi LIKE '%{4}%' AND Email LIKE '%{5}%' AND GioiTinh LIKE '%{6}%'", _
                    gv.MaGV, gv.TenGV, gv.NgaySinh.ToString().Split(" ")(0), gv.SoDienThoai, gv.DiaChi, gv.Email, gv.GioiTinh)

        If (gv.NgaySinh = Nothing) Then
            sSelect = sSelect.Replace("AND NgaySinh LIKE '%" & gv.NgaySinh.ToString().Split(" ")(0) & "%'", "")
        End If
        
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(sSelect, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of GiaoVienDTO)

        While reader.Read()
            Dim rgv As New GiaoVienDTO
            rgv.MaGV = reader.GetString(0)
            rgv.TenGV = reader.GetString(1)
            rgv.NgaySinh = reader.GetDateTime(2)
            rgv.SoDienThoai = reader.GetString(3)
            rgv.DiaChi = reader.GetString(4)
            rgv.Email = reader.GetString(5)
            rgv.GioiTinh = reader.GetString(6)

            ds.Add(rgv)
        End While

        conn.Close()
        Return ds

    End Function
End Class
